﻿using Beryl.OrderChanger;
using Beryl.OrderControllers.DataStructural;
using Beryl.OrderControllers.DBUpdate;
using Beryl.Util;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Serialization;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Threading;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace Beryl.OrderControllers
{
    public class reWork
    {
        private static reWork instance = null;
        private static readonly object uniqueLock = new object();

        public static reWork Instance
        {
            get
            {
                lock (uniqueLock)
                {
                    if (instance == null)
                    {
                        instance = new reWork();
                    }
                }
                return instance;
            }
        }
        public reWork() { }

        public List<string> getReWorkFrist(string para)
        {
            List<string> list = new List<string>();
            Dictionary<string, string> dic = new Dictionary<string, string>();
            try
            {
                string strPicYZ;
                Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
                var k = jsonArr[0];
                string cProductDepNo = k["cProductDepNo"].ToString();
                string sql = "select MotypeCode,Description from APS_Motype ";
                if (cProductDepNo == "Z21" || cProductDepNo == "Z61")
                {
                    sql += string.Format(@"WHERE cProductDepNo = '{0}'", cProductDepNo);
                }
                strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询APS_Motype失败");
                }
                dic.Add("APS_Motype", strPicYZ);

                string sqlString = "SELECT DISTINCT cPRoutingNo,cIdentCode,cIdentDesc FROM APS_ReworkRouting_V ";
                switch (cProductDepNo)
                {
                    case "Z21":
                        sqlString += string.Format(@"where cIdentDesc not like '%固态%'");
                        break;
                    case "Z61":
                        sqlString += string.Format(@"where cIdentDesc like '%固态%'");
                        break;
                }
                strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sqlString));
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询APS_ReworkRouting_V失败");
                }
                dic.Add("APS_ReworkRouting_V", strPicYZ);
                List<string> strings = getQueryTbale(para);
                dic.Add("getQueryTbale", strings[0]);

                string sqlCustom = string.Format(@"SELECT * FROM GET_Customer");
                strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sqlCustom));
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询客户编号失败");
                }
                dic.Add("GET_Customer", strPicYZ);
                string jsonStr = JsonConvert.SerializeObject(dic);
                jsonStr = "[" + jsonStr + "]";
                list.Add(jsonStr);
            }
            catch (Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //查询datatable数据
        public List<string> getQueryTbale(string para)
        {
            List<string> strings = new List<string>();
            try
            {
                Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
                var k = jsonArr[0];
                string startTime = k["startTime"].ToString();
                string endTime = k["endTime"].ToString();
                string cProductDepNo = k["cProductDepNo"].ToString();
                string sql = string.Format(@"SELECT a.cReworkNo,a.cReasStatus,a.dReasDate,a.cMatNo,a.dQty,a.cCusName,a.cReworkMOLotNo,a.cBegProDate,a.cEndProDate," +
                        "a.cProRoutingNo,a.reWorkReason,a.cSourceNo, a.cSpecifution,a.MotypeCode, a.Description,c.cPOrderNo " +
                "FROM APS_Rework a" +
                " LEFT JOIN APS_PlanOandSaleORelat c ON c.cReworkNo = a.cReworkNo" +
                " where a.dDate >='{0}' and a.dDate <='{1}'", startTime, endTime);
                if (k["reWorkNum"].ToString() != "")
                {
                    sql += string.Format(@"and c.cPOrderNo = '{0}'", k["reWorkNum"].ToString());
                }
                if (k["cSpecifution"].ToString() != "")
                {
                    sql += string.Format(@"and a.cSpecifution like '%{0}%'", k["cSpecifution"].ToString());
                }
                switch (cProductDepNo)
                {
                    case "Z21":
                        sql += string.Format(@"and a.cMatNo not LIKE 'C01%'");
                        break;
                    case "Z61":
                        sql += string.Format(@"and a.cMatNo LIKE 'C01%'");
                        break;
                }
                sql += " order by a.createTime desc;";
                string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if(strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询数据为空");
                }
                strings.Add(strPicYZ);
            }
            catch (Exception e)
            {
                strings.Add(e.Message);
            }
            return strings;
        }

        //销售订单查询
        public List<string> reWorkcSoNoQuery(string para)
        {
            List<string> lists = new List<string>();
            Dictionary<string, string> dic = new Dictionary<string, string>();
            try
            {
                string soNo = "";
                Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
                var k = jsonArr[0];
                string cProductDepNo = k["cProductDepNo"].ToString();
                string cSoNo = k["cSoNo"].ToString();
                string cSoNo_num = k["cSoNo_num"].ToString();
                string matType;
                string strPicYZ;
                switch (cProductDepNo)
                {
                    case "Z21":
                        matType = "C02%";//液态产品
                        break;
                    case "Z61":
                        matType = "C01%";//液态产品
                        break;
                    default:
                        throw new Exception("请选择固液态产品");
                }
                string sql = string.Format(@"select cSONo from APS_ViewSOrder where cSONo like '%{0}%_{1}' and cMatNo like '{2}';", cSoNo, cSoNo_num, matType);

                strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询SoNo数据失败");
                }
                else
                {
                    DataTable dt = (DataTable)JsonConvert.DeserializeObject(strPicYZ, (typeof(DataTable)));
                    DataRow[] dataRow = dt.Select("1 = 1");
                    foreach (DataRow row in dataRow)
                    {
                        string rowData = row["cSONo"].ToString();
                        if (rowData.Split('_')[1] == cSoNo_num)
                        {
                            soNo = rowData;
                        }
                    }
                }
                dic.Add("APS_ViewSOrder", soNo);

                string sql1 = string.Format(@"SELECT a.cParentSONo,a.cMatNo,a.dQty,a.cSONo,a.cCusName,a.iRowNo,a.iSOsID,b.cSpecifucation FROM APS_ViewSOrder a" +
                    " LEFT JOIN APS_MatData_V b ON a.cMatNo = b.cMatNo WHERE a.cSONo = '{0}' and a.cMatNo like '{1}';", soNo, matType);
                strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql1));
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询SoNo数据失败2");
                }
                dic.Add("APS_ViewSOrder2", strPicYZ);
                string jsonStr = JsonConvert.SerializeObject(dic);
                jsonStr = "[" + jsonStr + "]";
                lists.Add(jsonStr);
            }
            catch (Exception e)
            {
                lists.Add(e.Message);
            }
            return lists;
        }


        //查询规格
        public List<string> QuerySpecifution(string para)
        {
            List<string> list = new List<string>();
            try
            {
                var sql = string.Format(@"SELECT cMatNo FROM APS_MatData_V WHERE cSpecifucation = '{0}';",para);
                string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if(strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询规格数据失败");
                }
                Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(strPicYZ);
                var k = jsonArr[0];
                string cMatNo = k["cMatNo"].ToString();
                list.Add(cMatNo);
            }
            catch(Exception e)
            {
                list.Add("Error" + e.Message);
            }
            return list;
        }

        //查询物料
        public List<string> QueryMatNo(string para)
        {
            List<string> list = new List<string>();
            try
            {
                var sql = string.Format(@"SELECT cSpecifucation FROM APS_MatData_V WHERE cMatNo = '{0}';", para);
                string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询物料数据失败");
                }
                Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(strPicYZ);
                var k = jsonArr[0];
                string cMatNo = k["cSpecifucation"].ToString();
                list.Add(cMatNo);
            }
            catch (Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //重工
        public List<string> reWorkDual(string para)
        {
            List<string> list = new List<string>();
            try
            {
                string sqlString = "";
                string sqlString1 = "";
                Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
                var k = jsonArr[0];
                string cProRoutingNo = "";
                string MotypeCode = "";
                string today = CommonMethod.Instance.getTime3();
                string createTime = CommonMethod.Instance.getTimes();
                string gylx = k["工艺路线"].ToString();
                string reWorkType = k["重工单类别"].ToString();
                string cMatNo = k["重工后物料编码"].ToString();
                string sorderOrder = k["销售订单号"].ToString();
                string iSOsID = k["iSOsID"].ToString();
                string cSpecifution = k["重工后规格"].ToString();
                string cCustom = k["顾客名称"].ToString();
                string cCustCode = k["顾客名称编号"].ToString();
                string PrReWorkNo = k["重工前单批号"].ToString();
                string PrcMatNo = k["重工前物料编号"].ToString();
                string dty = k["重工总需求"].ToString();
                string PrDty = k["重工前单批数量"].ToString();
                string reason = k["重工原因"].ToString();
                string matType = cMatNo.Substring(0, 3);
                if ((reWorkType.IndexOf("固态") != -1 && gylx.IndexOf("固态") != -1 && matType == "C01") || (reWorkType.IndexOf("固态") == -1 && gylx.IndexOf("固态") == -1 && matType != "C01"))
                {
                    if (sorderOrder != "")
                    {
                        string Sorder = string.Format(@"select cMatNo,cParentSONo,cSONo,iSOsID from APS_ViewSOrder where cSONo = '{0}' and iSOsID = '{1}';", sorderOrder, iSOsID);
                        var sqlSorder = CommonMethod.Instance.selectDatabase(Sorder);
                        var sqlSorderRow = sqlSorder.Select("1 = 1");
                        if (sqlSorderRow.Length != 1 && sqlSorderRow[0][0].ToString() != cMatNo)
                        {
                            throw new Exception("如不关联销售订单，请清空销售订单号，再转重工单");
                        }
                    }
                }
                else
                {
                    throw new Exception("料号、订单类别、工艺路线需一致");
                }

                string sqlA = string.Format(@"SELECT * FROM APS_Rework WHERE cReworkMOLotNo = '{0}' order by ID desc;", PrReWorkNo);
                var selectsql = CommonMethod.Instance.selectDatabase(sqlA);
                var selectsqlRow = selectsql.Select("1 = 1");
                string cReworkNo = PrReWorkNo + "R";
                int count = 0;
                if (selectsqlRow.Length > 0)
                {
                    count = selectsqlRow.Length;
                }
                cReworkNo = CommonMethod.Instance.sumcReworkNO(count, selectsqlRow[0]["cReworkNo"].ToString(), cReworkNo);

                string sqlGY = string.Format(@"SELECT DISTINCT cPRoutingNo,cIdentCode,cIdentDesc FROM APS_ReworkRouting_V where cIdentDesc = '{0}';", gylx);
                var selectGY = CommonMethod.Instance.selectDatabase(sqlGY);
                var selectGYRow = selectGY.Select("1 = 1");
                if (selectGYRow.Length > 0 && selectGYRow.Length == 1)
                {
                    cProRoutingNo = selectGYRow[0]["cPRoutingNo"].ToString();
                }
                else
                {
                    throw new Exception("查询工艺路线失败");
                }

                string sqlType = string.Format(@"select MotypeCode,Description from APS_Motype where Description = '{0}';", reWorkType);
                var selectType = CommonMethod.Instance.selectDatabase(sqlType);
                var selectTypeRow = selectType.Select("1 = 1");
                if (selectTypeRow.Length > 0 && selectTypeRow.Length == 1)
                {
                    MotypeCode = selectTypeRow[0]["MotypeCode"].ToString();
                }
                else
                {
                    throw new Exception("重工类别查询失败");
                }

                sqlString += string.Format(@"('{0}','重工单','{1}',{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}');", cReworkNo, cMatNo, dty, PrReWorkNo,
                    today, today, reason, cProRoutingNo, sorderOrder, cSpecifution, cCustom, today, today, MotypeCode, reWorkType, createTime);

                sqlString1 += string.Format(@"('{0}','{1}','{2}',{3},'{4}','1','{5}');", cReworkNo, PrcMatNo, PrReWorkNo, PrDty, today, reason);

                string sql = "INSERT INTO APS_Rework(cReworkNo,cReasStatus,cMatNo,dQty,cReworkMOLotNo,dDate,dMatComTime,reWorkReason,cProRoutingNo,cSourceNo,cSpecifution,cCusName,cBegProDate,cEndProDate,MotypeCode,Description,createTime) VALUES ";
                sql += sqlString;

                string sql1 = "INSERT INTO APS_ReworkMatList(dDemNo,cMatNo,cMOLotNo,dMatQty,dMatComTime,cMatType,reWorkReason) VALUES ";
                sql1 += sqlString1;

                string querySql = string.Format(@"select 成品编码,子件物料编码,存货大类编码,分子用量,分母用量,子件损耗率 from [dbo].[View_Bom] where 成品编码 = '{0}';", cMatNo);
                var selectSql = CommonMethod.Instance.selectDatabase(querySql);
                var selectSqlRow = selectSql.Select("1 = 1");
                List<Dictionary<string, string>> keys = new List<Dictionary<string, string>>();
                Dictionary<string, string> keyValuePairs = null;
                if (selectSqlRow.Length == 1)//有半成品
                {
                    string matno = selectSqlRow[0]["子件物料编码"].ToString();
                    string querySql1 = string.Format(@"select 成品编码,子件物料编码,存货大类编码,分子用量,分母用量,子件损耗率 from [dbo].[View_Bom] where 成品编码 = '{0}';", matno);
                    var selectSql1 = CommonMethod.Instance.selectDatabase(querySql1);
                    var selectSqlRow1 = selectSql1.Select("1 = 1");
                    if (selectSqlRow1.Length > 1)
                    {
                        for (int i = 0; i < selectSqlRow1.Length; i++)
                        {
                            var item = selectSqlRow1[i];
                            if (item[2].ToString() == "Y0112" || item[2].ToString() == "Y0118" || item[2].ToString() == "Y0119")
                            {
                                keyValuePairs = new Dictionary<string, string>
                                {
                                    { "cMatNo", item[1].ToString() },
                                    { "cCateNo", item[2].ToString() },
                                    { "dLength", item[3].ToString() },
                                    { "subQty", item[4].ToString() },
                                    { "compScrap", item[5].ToString() }
                                };
                                keys.Add(keyValuePairs);
                            }
                        }
                    }
                }
                //胶盖Y0112 铝壳Y0118  胶管Y0119
                if (selectSqlRow.Length > 1)
                {
                    for (int i = 0; i < selectSqlRow.Length; i++)
                    {
                        var item = selectSqlRow[i];
                        if (item[2].ToString() == "Y0112" || item[2].ToString() == "Y0118" || item[2].ToString() == "Y0119")
                        {
                            keyValuePairs = new Dictionary<string, string>
                            {
                                { "cMatNo", item[1].ToString() },
                                { "cCateNo", item[2].ToString() },
                                { "dLength", item[3].ToString() },
                                { "subQty", item[4].ToString() },
                                { "compScrap", item[5].ToString() }
                            };
                            keys.Add(keyValuePairs);
                        }
                    }
                }
                string insertSql = "";
                int len = gylx.Split('-').Length;
                var processArr = gylx.Split('-');
                for (int i = 0; i < len; i++)
                {
                    var processName = processArr[i];
                    if (processName == "剥铝壳")
                    {
                        foreach (var item in keys)
                        {
                            item.TryGetValue("cCateNo", out string cCateNo);
                            if (cCateNo == "Y0118" || cCateNo == "Y0112" || cCateNo == "Y0119")
                            {
                                item.TryGetValue("cMatNo", out string matNo);
                                item.TryGetValue("dLength", out string dLength);
                                item.TryGetValue("subQty", out string subQty);
                                item.TryGetValue("compScrap", out string compScrap);
                                float matQty = (float)((float.Parse(dLength) / float.Parse(subQty)) * (1 + float.Parse(compScrap) * 0.01) * float.Parse(dty));
                                insertSql += string.Format(@"insert into APS_ReworkMatList (dDemNo, cMatNo,dMatQty,cMatType) values ('{0}','{1}','{2}','2');", cReworkNo, matNo, matQty);
                            }
                        }
                    }
                    if (processName == "剥胶")
                    {
                        foreach (var item in keys)
                        {
                            item.TryGetValue("cCateNo", out string cCateNo);
                            if (cCateNo == "Y0119")
                            {
                                item.TryGetValue("cMatNo", out string matNo);
                                item.TryGetValue("dLength", out string dLength);
                                item.TryGetValue("subQty", out string subQty);
                                item.TryGetValue("compScrap", out string compScrap);
                                float matQty = (float)((float.Parse(dLength) / float.Parse(subQty)) * (1 + float.Parse(compScrap) * 0.01) * float.Parse(dty));
                                insertSql += string.Format(@"insert into APS_ReworkMatList (dDemNo, cMatNo,dMatQty,cMatType) values ('{0}','{1}','{2}','2');", cReworkNo, matNo, matQty);
                            }
                        }
                    }
                }
                string allSQL = sql + sql1 + insertSql;
                var connstr = SqlHelper.GetConnSting();
                SqlHelper.ExecuteNonQuery(connstr, CommandType.Text, allSQL);
                list.Add("[0]");
            }
            catch (Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //查询SerialNo
        public List<string> querySerial(string para)
        {
            List<string> list = new List<string>();
            List<string> data_p = new List<string>();
            Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
              
                string cProductDepNo = "";
                string cProductDepName = jsonArr[jsonArr.Count - 2].ToString();
                string loginUser = jsonArr[jsonArr.Count - 1].ToString();
                if (cProductDepName.StartsWith("固态"))
                {
                    cProductDepNo = "Z61";
                }
                else
                {
                    cProductDepNo = "Z21";
                }
                for (int i = 0; i < jsonArr.Count - 2; i++)
                {
                    var k = jsonArr[i];
                    if ((k["cReasStatus"].ToString() == "重工单" || k["cReasStatus"].ToString() == "已转计划订单") && k["cProRoutingNo"].ToString() != "")
                    {
                        data_p.Add(k["cReworkNo"].ToString());
                    }
                }

                if (data_p.Count > 0)
                {
                    string selectsql = "SELECT cType,iSerialNo,cDate FROM APS_SerialNo WHERE cType = 'PR';";
                    var selectSql = CommonMethod.Instance.selectDatabase(selectsql);
                    var selectSqlRow = selectSql.Select("1 = 1");
                    string planOrderNo = "";
                    string date = CommonMethod.Instance.getTime3();
                    string time = CommonMethod.Instance.getTimes();
                    float serialNum = 0;
                    if (selectSqlRow[0]["cDate"].ToString() == date)
                    {
                        serialNum = float.Parse(selectSqlRow[0]["iSerialNo"].ToString());
                    }
                    else
                    {
                        serialNum = 801;
                    }
                    string pad = "000";

                    string sqlid = "SELECT max(AutoID) as MaxID FROM APS_PlanOrder";
                    var QsqlId = CommonMethod.Instance.selectDatabase(sqlid);
                    var sqlIdRow = QsqlId.Select("1 = 1");
                    float AutoID = float.Parse(sqlIdRow[0]["MaxID"].ToString());

                    string sql0 = "INSERT INTO APS_PlanOrder (AutoID,cPOrderNo,cCusName,cCusNo,cStatusNo,cStaUpdateTime,cMatNo,dPlanQty,cProductDepNo,cProductDepName,dDeliveryDate,cReworkFlag,MotypeCode,Description,dProBatch,cMakeTime,dPlanQty1) VALUES ";
                    string sql1 = "INSERT INTO APS_PlanOandSaleORelat (cPOrderNo,cSourceNo,dPlanQty1,iRowNo,iSOsID,cProductDepNo,cReworkNo) VALUES ";
                    float str = 0;
                    string str0 = "";
                    string str1 = "";
                    for (int i = 0; i < data_p.Count; i++)
                    {
                        string cReworkNo = data_p[i];
                        for (int j = 0; j < jsonArr.Count - 2; j++)
                        {
                            if (cReworkNo == jsonArr[j]["cReworkNo"].ToString())
                            {
                                AutoID++;
                                str = ++serialNum;
                                serialNum = float.Parse(pad.Substring(0, pad.Length - str.ToString().Length) + str);
                                planOrderNo = date.Split('-')[0].Substring(date.Split('-')[0].Length - 2) + date.Split('-')[1] + date.Split('-')[2] + serialNum;
                                string cMatNo = jsonArr[j]["cMatNo"].ToString();

                                string querySql = string.Format(@"select dMatQty,dDemNo,cMatNo,cMOLotNo from APS_ReworkMatList where dDemNo = '{0}' and cMatType = '1';", cReworkNo);
                                var querySqlR = CommonMethod.Instance.selectDatabase(querySql);
                                var querySqlRow = querySqlR.Select("1 = 1");
                                float dPlanQty1 = 0;
                                if (querySqlRow.Length > 0)
                                {
                                    for (int n = 0; n < querySqlRow.Length; n++)
                                    {
                                        dPlanQty1 += float.Parse(querySqlRow[n][0].ToString());
                                    }
                                    float Num = 0;
                                    Num = float.Parse(jsonArr[j]["dQty"].ToString());
                                    string MotypeCode = jsonArr[j]["MotypeCode"].ToString();
                                    string Description = jsonArr[j]["Description"].ToString();
                                    string cSourceNo = jsonArr[j]["cSourceNo"].ToString();
                                    string cCusName = jsonArr[j]["cCusName"].ToString();

                                    str0 += string.Format(@"({0},'{1}','{2}','','0000','{3}','{4}',{5},'{6}','{7}','','1','{8}','{9}',{10},'{11}',{12}),", AutoID, planOrderNo, cCusName, time, cMatNo,
                                        Num, cProductDepNo, cProductDepName, MotypeCode, Description, Num, time, dPlanQty1);

                                    str1 += string.Format(@"('{0}','{1}',{2},'','','{3}','{4}'),", planOrderNo, cSourceNo, dPlanQty1, cProductDepNo, cReworkNo);
                                }
                                else
                                {
                                    throw new Exception("查询cReworkNo为null");
                                }
                            }
                        }
                    }
                    sql0 = sql0 + str0.Substring(0, str0.Length - 1);
                    sql0 = sql0 + ";";
                    sql1 = sql1 + str1.Substring(0, str1.Length - 1);
                    sql1 = sql1 + ";";
                    string updatesql = string.Format(@"UPDATE APS_SerialNo SET cDate = '{0}',iSerialNo = {1} WHERE cType = 'PR';", date, str);
                    string allSQL = sql0 + sql1 + updatesql;
                    var connstr = SqlHelper.GetConnSting();
                    SqlHelper.ExecuteNonQuery(connstr, CommandType.Text, allSQL);
                    changeOrderPc(data_p, loginUser);
                }else
                {
                    throw new Exception("行数少于或等于0");
                }
                list.Add("success");
            }
            catch (Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //转生产批次
        public void changeOrderPc(List<string> data_p, string loginUser)
        {
            List<string> list = new List<string>();
            var PlanOrder = new Dictionary<string, List<Dictionary<string, string>>>();
            try
            {
                string retID = "";
                for (int i = 0; i < data_p.Count; i++)
                {
                    if (i != data_p.Count - 1)
                    {
                        retID += "'" + data_p[i] + "'" + ",";
                    }
                    else
                    {
                        retID += "'" + data_p[i] + "'";
                    }
                }
                string PlanAndSorder = string.Format(@"SELECT cPOrderNo,cSourceNo,cProductDepNo,dPlanQty1,cReworkNo from APS_PlanOandSaleORelat where cReworkNo IN ({0});", retID);
                var PlanAndSorderRow = CommonMethod.Instance.retData(PlanAndSorder, "", "");

                string Rework = string.Format(@"SELECT a.cReworkNo,a.cReasStatus,a.dReasDate,a.dDate,a.cMatNo,a.dQty,a.cSourceNo,a.dMatComTime,a.cProRoutingNo,a.cReworkFlag," +
                            "a.cReworkMOLotNo,a.cWorkFlowID,a.iFlag,a.cSpecifution,a.reWorkReason,a.note,a.MotypeCode, b.cMatNo as cMatandResNo, b.dMatQty,b.dDemNo,b.cMOLotNo," +
                           "b.cMatType FROM APS_Rework a LEFT JOIN APS_ReworkMatList b ON a.cReworkNo = b.dDemNo WHERE a.cReworkNo IN ({0});", retID);
                var ReworkRow = CommonMethod.Instance.retData(Rework, "", "");

                string MatData = string.Format(@"SELECT APS_Rework.cReworkNo,APS_Rework.cMatNo,APS_MatData_V.cProductDepNo,APS_MatData_V.cProductDepName,APS_MatData_V.dProBatch FROM APS_MatData_V" +
                        " LEFT JOIN APS_Rework ON APS_MatData_V.cMatNo = APS_Rework.cMatNo WHERE cReworkNo IN ({0});", retID);
                var MatDataRow = CommonMethod.Instance.retData(MatData, "", "");

                var PlanAndReworkObj = reWorkStructural.Instance.getPlanAndRework(PlanAndSorderRow);
                var reworkObj = reWorkStructural.Instance.getRework(ReworkRow);
                var MatDataObj = reWorkStructural.Instance.getMatData(MatDataRow);

                Dictionary<string, string> planOrders = new Dictionary<string, string>();
                foreach (var cReworkNo in reworkObj.Keys)
                {
                    var dataRow = PlanAndReworkObj[cReworkNo];
                    string cPOrderNo = dataRow["cPOrderNo"].ToString();
                    if (!PlanOrder.ContainsKey(cReworkNo))
                    {
                        PlanOrder.Add(cPOrderNo, new List<Dictionary<string, string>>());
                    }
                    foreach (var cMatNo in reworkObj[cReworkNo].Keys)
                    {
                        foreach (var cMatandResNo in reworkObj[cReworkNo][cMatNo].Keys)
                        {
                            for (int i = 0; i < reworkObj[cReworkNo][cMatNo][cMatandResNo].Count; i++)
                            {
                                planOrders = new Dictionary<string, string>();
                                string dPlanQty = reworkObj[cReworkNo][cMatNo][cMatandResNo][i]["dMatQty"].ToString();
                                string dReqQty = dPlanQty;
                                string cBatchNumber = reworkObj[cReworkNo][cMatNo][cMatandResNo][i]["cReworkMOLotNo"].ToString();
                                string cSpecifution = reworkObj[cReworkNo][cMatNo][cMatandResNo][i]["cSpecifution"].ToString();
                                planOrders.Add("cReworkNo", cReworkNo);
                                planOrders.Add("cOrderType", "0");
                                planOrders.Add("cMatNo", cMatNo);
                                planOrders.Add("cMatandResNo", cMatandResNo);
                                planOrders.Add("dPlanQty", dPlanQty);
                                planOrders.Add("cSpecifution", cSpecifution); 
                                planOrders.Add("cBatchNumber", cBatchNumber);
                                planOrders.Add("dReqQty", dReqQty);
                                PlanOrder[cPOrderNo].Add(planOrders);
                            }
                        }
                    }
                }
                reWorkUpdate.Instance.updatePlanOrder(PlanOrder);
                ReworkPro(retID, loginUser);
                list.Add("success");
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        //更新状态
        private void ReworkPro(string retID, string UserName)
        {
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> proOrderObj = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>();
            try
            {
                string planOrder = string.Format(@"SELECT APS_PlanOrder.cPOrderNo,APS_PlanOrder.cMatNo,APS_PlanOrder.dPlanQty,APS_PlanOrder.dPlanQty1,APS_PlanOandSaleORelat.cSourceNo," +
                           "APS_PlanOandSaleORelat.cReworkNo,APS_PlanOrder.dDeliveryDate, APS_PlanOrder.cProductDepNo,APS_PlanOrder.cReworkFlag,APS_PlanOrder.cBigBegProDate,APS_PlanOrder.MotypeCode," +
                            "APS_PlanOrder.dProBatch FROM APS_PlanOrder LEFT JOIN APS_PlanOandSaleORelat ON APS_PlanOandSaleORelat.cPOrderNo = APS_PlanOrder.cPOrderNo" +
                        " WHERE APS_PlanOandSaleORelat.cReworkNo IN ({0});", retID);
                var planOrderRow = CommonMethod.Instance.retData(planOrder, "", "");

                string PlanAndSorder = string.Format(@"SELECT cPOrderNo,cSourceNo,cProductDepNo,dPlanQty1,cReworkNo from APS_PlanOandSaleORelat where cReworkNo IN ({0});", retID);
                var PlanAndSorderRow = CommonMethod.Instance.retData(PlanAndSorder, "", "");

                string Rework = string.Format(@"SELECT a.cReworkNo,a.cReasStatus,a.dReasDate,a.dDate,a.cMatNo,a.dQty,a.cSourceNo,a.dMatComTime,a.cProRoutingNo,a.cReworkFlag," +
                           "a.cReworkMOLotNo,a.cWorkFlowID,a.iFlag,a.cSpecifution,a.reWorkReason,a.note,a.MotypeCode, b.cMatNo as cMatandResNo, b.dMatQty,b.dDemNo,b.cMOLotNo," +
                          "b.cMatType FROM APS_Rework a LEFT JOIN APS_ReworkMatList b ON a.cReworkNo = b.dDemNo WHERE a.cReworkNo IN ({0});", retID);
                var ReworkRow = CommonMethod.Instance.retData(Rework, "", "");

                string MatData = string.Format(@"SELECT APS_Rework.cReworkNo,APS_Rework.cMatNo,APS_MatData_V.cProductDepNo,APS_MatData_V.cProductDepName,APS_MatData_V.dProBatch FROM APS_MatData_V" +
                       " LEFT JOIN APS_Rework ON APS_MatData_V.cMatNo = APS_Rework.cMatNo WHERE cReworkNo IN ({0});", retID);
                var MatDataRow = CommonMethod.Instance.retData(MatData, "", "");

                string ReworkRouting = string.Format(@"SELECT cPRoutingNo,cIdentCode,cIdentDesc,cOpSeq,cOperationNo,cOperationName FROM APS_ReworkRouting_V;");
                var ReworkRoutRow = CommonMethod.Instance.retData(ReworkRouting, "", "");

                var PlanAndReworkObj = reWorkStructural.Instance.getPlanAndRework(PlanAndSorderRow);
                var reworkObj = reWorkStructural.Instance.getRework(ReworkRow);
                var MatDataObj = reWorkStructural.Instance.getMatData(MatDataRow);
                var planOrderObj = reWorkStructural.Instance.getPlanOrder(planOrderRow);
                var ReworkRoutingObj = reWorkStructural.Instance.getReworkRouting(ReworkRoutRow);
                             
                //未写
                //string APS_Valid = string.Format(@"SELECT cOrderNo,cMatNo,dPlanQty,cOperationNo,cOperationName,cOpSeq,cMatandResNo,cMatandResName,cMatandResType," +
                //             "dWidth,dLength,cSpecifution,cUnit,dReqQty,cCapacityLow,cCapacityUp,cCapacity,cBomid,cCateNo,dVoltage,Unit,cSortSeq  FROM APS_ValidBOM WHERE cOrderNo = '{0}';", cplanOrder);
                //var ValidRow = CommonMethod.Instance.retData(APS_Valid, "", "");
                //var ValidBOMObj = CommonMethod.Instance.getValidBOM(ValidRow);

                //CommonMethod.Instance.Timer_Start();
                //Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> ValidBOMObj = CommonMethod.VaildBom;

                string today = CommonMethod.Instance.getTime3();
                var planOrderObjPara = new Dictionary<string, Dictionary<string, string>>();
                foreach (var cReworkNo in reworkObj.Keys)
                {
                    // 生产订单号
                    string cPOrderNo = PlanAndReworkObj[cReworkNo]["cPOrderNo"].ToString();
                    string proOrderNo = cPOrderNo;
                    string cProductDepNo = planOrderObj[cPOrderNo]["cProductDepNo"].ToString();
                    // 交货期
                    string dDeliveryDate = planOrderObj[cPOrderNo]["dDeliveryDate"];
                    string MotypeCode = planOrderObj[cPOrderNo]["MotypeCode"];
                    string cMatNo1 = planOrderObj[cPOrderNo]["cMatNo"];
                    string str = "";
                    if (cProductDepNo == "Z61")
                    {
                        str = MotypeCode;
                    }
                    else if (cProductDepNo == "Z21")
                    {
                        str = MotypeCode;
                    }
                    Dictionary<string, Dictionary<string, string>> pairs = new Dictionary<string, Dictionary<string, string>>();
                    foreach (var cMatNo in reworkObj[cReworkNo].Keys)
                    {
                        if (!proOrderObj.ContainsKey(proOrderNo))
                        {
                            pairs = new Dictionary<string, Dictionary<string, string>>();
                            proOrderObj.Add(proOrderNo, null);
                        }
                        int serialNum = 0;
                        foreach (var cMatandResNo in reworkObj[cReworkNo][cMatNo].Keys)
                        {
                            // 初始拆批量
                            for (int i = 0; i < reworkObj[cReworkNo][cMatNo][cMatandResNo].Count; i++)
                            {
                                string cMatType = reworkObj[cReworkNo][cMatNo][cMatandResNo][i]["cMatType"].ToString();
                                if (cMatType == "2")
                                {
                                    continue;
                                }
                                string batchNum = reworkObj[cReworkNo][cMatNo][cMatandResNo][i]["dMatQty"].ToString();
                                string cMOLotNo = reworkObj[cReworkNo][cMatNo][cMatandResNo][i]["cMOLotNo"].ToString();
                                if (batchNum == null)
                                {
                                    throw new Exception("请到ERP维护拆批量");
                                }
                                // 批次的流水号
                                serialNum = CommonMethod.Instance.GenerateID2(++serialNum, "000");
                                // 批次编号
                                string proLotOrderNo = cPOrderNo + str + "-" + serialNum;
                                Dictionary<string, string> keys = new Dictionary<string, string>();
                                proOrderObj.TryGetValue(cPOrderNo, out Dictionary<string, Dictionary<string, string>> para);
                                if (para == null || !para.ContainsKey(proLotOrderNo))
                                {
                                    keys = new Dictionary<string, string>();
                                    pairs.Add(proLotOrderNo, null);
                                    keys.Add("cProductDepNo", cProductDepNo);
                                    keys.Add("dDeliveryDate", dDeliveryDate);
                                    pairs[proLotOrderNo] = keys;
                                    proOrderObj[cPOrderNo] = pairs;
                                }
                                // 判断订单需求数量是否大于拆批量
                                if (float.Parse(planOrderObj[cPOrderNo]["dPlanQty1"]) > float.Parse(batchNum))
                                {
                                    // 批次数量
                                    proOrderObj[proOrderNo][proLotOrderNo].Add("batchNum", float.Parse(planOrderObj[cPOrderNo]["dPlanQty1"]) > float.Parse(batchNum) ? batchNum : planOrderObj[cPOrderNo]["dPlanQty1"]);
                                    proOrderObj = reWorkStructural.Instance.TraverseSource(cPOrderNo, planOrderObj, batchNum, proOrderObj, proLotOrderNo
                                                , UserName, serialNum, cMOLotNo, out planOrderObjPara);
                                    planOrderObj = planOrderObjPara;
                                    // 按拆批量扣减需求
                                    float ret = float.Parse(planOrderObj[cPOrderNo]["dPlanQty1"]);
                                    ret -= float.Parse(batchNum);
                                    planOrderObj[cPOrderNo]["dPlanQty1"] = ret.ToString();
                                }
                                else
                                {
                                    // 最后一批批次数量
                                    proOrderObj[proOrderNo][proLotOrderNo]["batchNum"] = planOrderObj[cPOrderNo]["dPlanQty1"];
                                    // 更新拆批量
                                    batchNum = planOrderObj[cPOrderNo]["dPlanQty1"];
                                    proOrderObj = reWorkStructural.Instance.TraverseSource(cPOrderNo, planOrderObj, batchNum, proOrderObj, proLotOrderNo
                                                , UserName, serialNum, cMOLotNo, out planOrderObjPara);
                                    planOrderObj = planOrderObjPara;
                                    planOrderObj[cPOrderNo]["dPlanQty1"] = "0";
                                }
                            }
                        }
                    }
                }
                var proLotMatQtyObj = reWorkStructural.Instance.getBatchMatReworkQty(proOrderObj, PlanAndSorderRow, ReworkRow);
                var proRoutingObj = reWorkStructural.Instance.getProRouting(proLotMatQtyObj, ReworkRoutingObj);
                // 删除旧表信息
                reWorkUpdate.Instance.deleteProLot(proOrderObj);
                // 输出写入
                reWorkUpdate.Instance.updateProOrder(proOrderObj);
                reWorkUpdate.Instance.insertBatchMatTotalQty(proLotMatQtyObj);
                reWorkUpdate.Instance.insertProRouting(proRoutingObj);
                reWorkUpdate.Instance.updateRework(reworkObj);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}